/*
 * State.java
 * This file maintains two persistent object through JDBC to mysql database.
 * Two data table are arraylist composed of records of AppServerInfo and ConnectionInfo 
 * 
 * */

package wps.servicebus.control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

// Application server data

class AppServerInfo
{
	private String m_addrServ = null;	// Server address
	private int m_portAgent = 0;		// Port of agent instance
	private int m_nInstance = 0;		// Number of server instance
	
	public AppServerInfo(String addrServ)
	{
		setM_addrServ(addrServ);		
	}
	
	public AppServerInfo(){}
	
	public AppServerInfo(String addrServ, int portAgent, int nInstance){
		this.setM_addrServ(addrServ);
		this.setM_portAgent(portAgent);
		this.setM_nInstance(nInstance);
	}
	
	public void setM_addrServ(String m_addrServ) {
		this.m_addrServ = m_addrServ;
	}

	public String getM_addrServ() {
		return m_addrServ;
	}

	public void setM_portAgent(int m_portAgent) {
		this.m_portAgent = m_portAgent;
	}

	public int getM_portAgent() {
		return m_portAgent;
	}

	public void setM_nInstance(int m_nInstance) {
		this.m_nInstance = m_nInstance;
	}

	public int getM_nInstance() {
		return m_nInstance;
	}
	
	
	
}

// Application server table
class AppServerTable
{	
	
	private String conString = "jdbc:mysql:///db_servicebus";
	private String username = "root";
	private String password = "thu";	
	
	public AppServerTable(){}

	
	public boolean addAppServerInfo(AppServerInfo instance){
		Statement st = null;
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			st = con.createStatement();
			String insert_sql = "INSERT INTO tbl_appserver VALUES(null, '@m_addrServ', @m_portAgent, @m_nInstance, NOW(), NOW(), 0)";
			insert_sql = insert_sql.replaceAll("@m_addrServ", instance.getM_addrServ());
			insert_sql = insert_sql.replaceAll("@m_portAgent", Integer.toString(instance.getM_portAgent()));
			insert_sql = insert_sql.replaceAll("@m_nInstance", Integer.toString(instance.getM_nInstance()));
		
			st.executeUpdate(insert_sql);
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
		
		
	}
	
	public ArrayList<AppServerInfo> readAppServers(){
		Connection con = null;
		Statement st = null;
		ArrayList<AppServerInfo> appServerList = new ArrayList<AppServerInfo>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			String query = "SELECT * FROM tbl_appserver WHERE isDeleted = 0";
			st = con.createStatement();
			ResultSet result = st.executeQuery(query);
			while(result.next()){
				AppServerInfo appserver = new AppServerInfo();
				appserver.setM_addrServ(result.getString("m_addrServ"));
				appserver.setM_portAgent(result.getInt("m_portAgent"));
				appserver.setM_nInstance(result.getInt("m_nInstance"));
				appServerList.add(appserver);
			}
			con.close();
			return appServerList;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();			
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			return appServerList;
		}
	}	
	
	public boolean updateAppServer_nInstance(String m_addrServ, int m_portAgent, int increment){
		Statement st = null;
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			st = con.createStatement();
			String update_sql = "UPDATE tbl_appserver SET m_nInstance=(m_nInstance+(@increment)), updateTime=NOW() WHERE m_addrServ='@m_addrServ' AND m_portAgent=@m_portAgent AND isDeleted = 0";
			update_sql = update_sql.replaceAll("@increment", Integer.toString(increment));
			update_sql = update_sql.replaceAll("@m_addrServ", m_addrServ);
			update_sql = update_sql.replaceAll("@m_portAgent", Integer.toString(m_portAgent));
		
			st.executeUpdate(update_sql);
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
	}

	public boolean deleteAllAppServer(){
		Statement st = null;
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			st = con.createStatement();
			String delete_sql = "UPDATE tbl_appserver SET isDeleted=1, updateTime=NOW() WHERE isDeleted = 0";			
		
			st.executeUpdate(delete_sql);
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
	}
	
	public AppServerInfo topByCondition(String condition){
		Connection con = null;
		Statement st = null;
		AppServerInfo appserver = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			String query = "SELECT * FROM tbl_appserver WHERE isDeleted = 0 ORDER BY @condition ASC LIMIT 1";
			query = query.replaceAll("@condition", condition);
			st = con.createStatement();
			ResultSet result = st.executeQuery(query);
			while(result.next()){
				appserver = new AppServerInfo();
				appserver.setM_addrServ(result.getString("m_addrServ"));
				appserver.setM_portAgent(result.getInt("m_portAgent"));
				appserver.setM_nInstance(result.getInt("m_nInstance"));				
			}
			con.close();
			return appserver;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();			
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			return appserver;
		}
	}

	public AppServerInfo topByInstNumber(){
		return topByCondition("m_nInstance");
	}
}

// Active connection data
class ConnectionInfo
{
	private String m_token = null;		// User token
	private String m_key = null;		// Key for data transfer
	private String m_fileUri = null;	// Document URI
	private String m_user = null;		// User ID
	private String m_addrApp = null;	// Address of application server
	private int m_portApp = 0;			// Port of application instance
	private int m_pid = 0;				// Process ID of application instance
	private boolean isActive = false;	// if the connection is established between client and the application

	public String get_Key() { return getM_key(); }
	public String get_AppAddress() { return getM_addrApp(); }
	public int get_AppPort() { return getM_portApp(); }
	
	public ConnectionInfo(String m_addrApp, int m_portApp){
		this.setM_addrApp(m_addrApp);
		this.setM_portApp(m_portApp);
	}
	
	public ConnectionInfo(){}
	
	public void SetData(String user, String token, String fileUri,
			String key, String addrApp, int portApp, int pid, boolean isActive)
	{
		setM_user(user);
		setM_token(token);
		setM_fileUri(fileUri);
		setM_key(key);
		setM_addrApp(addrApp);
		setM_portApp(portApp);
		setM_pid(pid);
		setActive(isActive);
	}
	public void setM_token(String m_token) {
		this.m_token = m_token;
	}
	public String getM_token() {
		return m_token;
	}
	public void setM_key(String m_key) {
		this.m_key = m_key;
	}
	public String getM_key() {
		return m_key;
	}
	public void setM_fileUri(String m_fileUri) {
		this.m_fileUri = m_fileUri;
	}
	public String getM_fileUri() {
		return m_fileUri;
	}
	public void setM_user(String m_user) {
		this.m_user = m_user;
	}
	public String getM_user() {
		return m_user;
	}
	public void setM_addrApp(String m_addrApp) {
		this.m_addrApp = m_addrApp;
	}
	public String getM_addrApp() {
		return m_addrApp;
	}
	public void setM_portApp(int m_portApp) {
		this.m_portApp = m_portApp;
	}
	public int getM_portApp() {
		return m_portApp;
	}
	public void setM_pid(int m_pid) {
		this.m_pid = m_pid;
	}
	public int getM_pid() {
		return m_pid;
	}
	public void setActive(boolean isActive) {
		this.isActive = isActive;
	}
	public boolean isActive() {
		return isActive;
	}
}

// Active connection table
class ConnectionTable
{
	private String conString = "jdbc:mysql:///db_servicebus";
	private String username = "root";
	private String password = "thu";	
	
	public ConnectionTable(){}

	
	public boolean addConnectionInfo(ConnectionInfo instance){
		Statement st = null;
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			st = con.createStatement();
			String insert_sql = "INSERT INTO tbl_connectioninfo VALUES(null, @m_token, @m_key, @m_fileUri, @m_user, @m_addrApp, @m_portApp, @m_pid, @isActive, @isDeleted)";
			insert_sql = insert_sql.replaceAll("@m_token", (instance.getM_token() == null ? "null" : "'"+ instance.getM_token() + "'"));
			insert_sql = insert_sql.replaceAll("@m_key", (instance.getM_key() == null ? "null" : "'" + instance.getM_key() + "'"));
			insert_sql = insert_sql.replaceAll("@m_fileUri", (instance.getM_fileUri() == null ? "null" : "'" + instance.getM_fileUri() + "'"));
			insert_sql = insert_sql.replaceAll("@m_user", (instance.getM_user() == null ? "null" : "'" + instance.getM_user() + "'"));
			insert_sql = insert_sql.replaceAll("@m_addrApp", (instance.getM_addrApp() == null ? "null" : "'" + instance.getM_addrApp() + "'"));
			insert_sql = insert_sql.replaceAll("@m_portApp", Integer.toString(instance.getM_portApp()));
			insert_sql = insert_sql.replaceAll("@m_pid", Integer.toString(instance.getM_pid()));
			insert_sql = insert_sql.replaceAll("@isActive", "0");
			insert_sql = insert_sql.replaceAll("@isDeleted", "0");			
		
			st.executeUpdate(insert_sql);
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
	}
	
	public boolean startConnectionInfo(String addr, int port){
		Statement st = null;
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			st = con.createStatement();
			String update_sql = "UPDATE tbl_connectioninfo SET isActive=1 WHERE m_addrApp='@m_addrApp' AND m_portApp=@m_portApp AND isDeleted = 0";
			update_sql = update_sql.replaceAll("@m_addrApp", addr);
			update_sql = update_sql.replaceAll("@m_portApp", Integer.toString(port));
		
			st.executeUpdate(update_sql);
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
	}
	
	public boolean destroyConnectionInfo(String addr, int port){
		Statement st = null;
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			st = con.createStatement();
			String update_sql = "UPDATE tbl_connectioninfo SET isActive=0, isDeleted=1 WHERE m_addrApp='@m_addrApp' AND m_portApp=@m_portApp AND isDeleted = 0";
			update_sql = update_sql.replaceAll("@m_addrApp", addr);
			update_sql = update_sql.replaceAll("@m_portApp", Integer.toString(port));		
			st.executeUpdate(update_sql);
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
	}

	public boolean deleteAllConnectionInfo(){
		Statement st = null;
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			st = con.createStatement();
			String update_sql = "UPDATE tbl_connectioninfo SET isDeleted=1 WHERE isDeleted = 0";
			st.executeUpdate(update_sql);
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
	}
	
	public ArrayList<ConnectionInfo> readAllConnections(){
		Connection con = null;
		Statement st = null;
		ArrayList<ConnectionInfo> connectionList = new ArrayList<ConnectionInfo>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(conString, username, password);
			String query = "SELECT * FROM tbl_connectioninfo WHERE isDeleted = 0";
			st = con.createStatement();
			ResultSet result = st.executeQuery(query);
			while(result.next()){
				ConnectionInfo connection = new ConnectionInfo();
				connection.setActive(result.getBoolean("isActive"));
				connection.setM_addrApp(result.getString("m_addrApp"));
				connection.setM_portApp(result.getInt("m_portApp"));
				connection.setM_pid(result.getInt("m_pid"));			
				if(result.getString("m_token") != null){
					connection.setM_token(result.getString("m_token"));
				}
				if(result.getString("m_key") != null){
					connection.setM_key(result.getString("m_key"));
				}
				if(result.getString("m_fileUri") != null){
					connection.setM_fileUri(result.getString("m_fileUri"));
				}
				if(result.getString("m_user") != null){
					connection.setM_user(result.getString("m_user"));
				}
				connectionList.add(connection);
			}
			con.close();
			return connectionList;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();			
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			return connectionList;
		}
	}	
}

public class State
{
	public static void main(String[] args)
	{
		System.out.println("State Class Unit Test");
//		AppServerTable appServerTable = new AppServerTable();
//		appServerTable.deleteAllAppServer();
//		appServerTable.addAppServerInfo(new AppServerInfo("127.0.0.1", 4570, 9));
//		appServerTable.updateAppServer_nInstance("127.0.0.1", 4570, 1);
//		ArrayList<AppServerInfo> result = appServerTable.readAppServers();		
//		for(AppServerInfo inst : result){
//			System.out.println("addr: " + inst.getM_addrServ());
//			System.out.println("port: " + inst.getM_portAgent());
//			System.out.println("instance number: " + inst.getM_nInstance());
//		}
//		AppServerInfo appserver = appServerTable.topByCondition("m_nInstance");
//		System.out.println(appserver.getM_portAgent());		
		
//		ConnectionTable connectionTable = new ConnectionTable();
//		connectionTable.addConnectionInfo(new ConnectionInfo("127.0.0.1", 10001));
//		connectionTable.startConnectionInfo("127.0.0.1", 10001);
//		connectionTable.destroyConnectionInfo("127.0.0.1", 10001);
	}
}